Linux Cubed Series 2: Applications/Linux Cubed Series 2 - Applications.iso/database/postgres/appgen-0.2-a/appgen-0/AppGEN/apps/cis.app

Text File | 1996-07-11 | 31.8 KB | 1,094 lines

INCLUDE "global.app"

INCLUDE "cis.menu"
INCLUDE "cis.rep"

[MODULE subjective]
    SET default_process AS "clear"

[FORM subjective]
    SET title AS "Subjective Costcodes"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        costcode : 7 : "Costcode: " : TEXT
        description : 45 : "Description: " : TEXT
        sub_type : 10 : "Type: " : CHOICE "Building, Engineering" "Furniture, Equipment" "Professional Fees" "Other"
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "system"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        costcode : 7 : "Costcode: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    SQL "SELECT * FROM subjective WHERE  costcode='$input.costcode';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY subjective WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT max(recnum) FROM subjective;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO subjective (recnum,costcode,description,sub_type) VALUES ($new_recnum , '$input.costcode', '$input.description', $input.sub_type);"
    DISPLAY subjective

[PROCESS update]
    DATA results
    INT no_rows
    SQL "UPDATE subjective SET costcode='$input.costcode',description='$input.description',sub_type=$input.sub_type WHERE recnum = $input.recnum ;"
    DISPLAY subjective

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY subjective

[PROCESS yes]
    SQL "DELETE FROM subjective WHERE recnum = $input.recnum ;"
    DISPLAY subjective

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT recnum, costcode, description FROM subjective WHERE " TO query
    IF NOT BLANK(input.costcode) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "costcode like '%" input.costcode "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.description) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "description like '%" input.description "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY costcode, description;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, costcode,description,sub_type FROM subjective WHERE recnum=$input.recnum ;" TO results
    DISPLAY subjective WITH results

[PROCESS pick_display]
    DISPLAY subjective WITH input

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        costcode : LEFT : "costcode"
        description : LEFT : "description"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE supplier]
    SET default_process AS "clear"

[FORM supplier]
    SET title AS "Supplier Details"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        ref : 4 : "Reference: " : TEXT
        name : 45 : "Name: " : TEXT
        addr1 : 45 : "Address: " : TEXT
        addr2 : 30 : "" : TEXT
        addr3 : 30 : "" : TEXT
        addr4 : 30 : "" : TEXT
        tel : 15 : "Telephone: " : TEXT
        fax : 15 : "Fax: " : TEXT
        supplier_type : 10 : "Type: " : CHOICE "Contractor" "Consultant" "Supplier"
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "invoices"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        ref : 8 : "Reference: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    SQL "SELECT * FROM supplier WHERE  ref='$input.ref';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY supplier WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT max(recnum) FROM supplier;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO supplier (recnum,ref,name,addr1,addr2,addr3,addr4,tel,fax,supplier_type) VALUES ($new_recnum , '$input.ref', '$input.name', '$input.addr1', '$input.addr2', '$input.addr3', '$input.addr4', '$input.tel', '$input.fax', $input.supplier_type);"
    DISPLAY supplier

[PROCESS update]
    DATA results
    INT no_rows
    SQL "UPDATE supplier SET ref='$input.ref',name='$input.name',addr1='$input.addr1',addr2='$input.addr2',addr3='$input.addr3',addr4='$input.addr4',tel='$input.tel',fax='$input.fax',supplier_type=$input.supplier_type WHERE recnum = $input.recnum ;"
    DISPLAY supplier

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY supplier

[PROCESS yes]
    SQL "DELETE FROM supplier WHERE recnum = $input.recnum ;"
    DISPLAY supplier

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT recnum, ref, name FROM supplier WHERE " TO query
    IF NOT BLANK(input.ref) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "ref like '%" input.ref "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.name) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "name like '%" input.name "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.addr1) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "addr1 like '%" input.addr1 "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.addr2) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "addr2 like '%" input.addr2 "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.addr3) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "addr3 like '%" input.addr3 "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.addr4) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "addr4 like '%" input.addr4 "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.tel) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "tel like '%" input.tel "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.fax) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "fax like '%" input.fax "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY ref, name;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, ref,name,addr1,addr2,addr3,addr4,tel,fax,supplier_type FROM supplier WHERE recnum=$input.recnum ;" TO results
    DISPLAY supplier WITH results

[PROCESS pick_display]
    DISPLAY supplier WITH input

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        ref : LEFT : "ref"
        name : LEFT : "name"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE programme]
    SET default_process AS "clear"

[FORM programme]
    SET title AS "Programme Codes"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        code : 3 : "Code: " : TEXT
        description : 45 : "Description: " : TEXT
        prog_type : 10 : "Type: " : CHOICE "Conditional" "Unconditional" "Non Capital"
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "system"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        code : 3 : "Code: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    SQL "SELECT * FROM programme WHERE code='$input.code';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY programme WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT max(recnum) FROM programme;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO programme (recnum,code,description,prog_type) VALUES ($new_recnum , '$input.code', '$input.description', $input.prog_type);"
    DISPLAY programme

[PROCESS update]
    DATA results
    INT no_rows
    SQL "UPDATE programme SET code='$input.code',description='$input.description',prog_type=$input.prog_type WHERE recnum = $input.recnum ;"
    DISPLAY programme

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY programme

[PROCESS yes]
    SQL "DELETE FROM programme WHERE recnum = $input.recnum ;"
    DISPLAY programme

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT recnum, code, description FROM programme WHERE " TO query
    IF NOT BLANK(input.code) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "code like '%" input.code "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.description) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "description like '%" input.description "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY code, description;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, code,description,prog_type FROM programme WHERE recnum=$input.recnum ;" TO results
    DISPLAY programme WITH results

[PROCESS pick_display]
    DISPLAY programme WITH input

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        code : LEFT : "code"
        description : LEFT : "description"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE scheme]
    SET default_process AS "clear"

[FORM scheme]
    SET title AS "Scheme Details"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        scheme_id : 6 : "Scheme Number: " : TEXT
        title : 45 : "Title: " : TEXT
        programme_code : 3 : "Programme: " : PICK PROCESS "pick_programme_code"
        costcode : 7 : "Costcode: " : TEXT
        status : 10 : "Status: " : CHOICE "Dormant" "Feasibility" "Design" "Tender" "Construction" "Commissioning" "Completed"
        manager : 6 : "Scheme Manager: " : PICK PROCESS "pick_manager"
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "schemes"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        scheme_id : 6 : "Scheme Number: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }


[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    SQL "SELECT * FROM scheme WHERE  scheme_id='$input.scheme_id';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY scheme WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT code from programme WHERE code='$input.programme_code' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY scheme WITH input ERROR "Foreign Key Not Found" ON 3
        END
    SQL "SELECT ref from user WHERE ref='$input.manager' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY scheme WITH input ERROR "Foreign Key Not Found" ON 6
        END
    SQL "SELECT max(recnum) FROM scheme;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO scheme (recnum,scheme_id,title,programme_code,costcode,status,manager) VALUES ($new_recnum , '$input.scheme_id', '$input.title', '$input.programme_code', '$input.costcode', $input.status, '$input.manager');"
    DISPLAY scheme

[PROCESS update]
    DATA results
    INT no_rows
    SQL "SELECT code from programme WHERE code='$input.programme_code';" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY scheme WITH input ERROR "Foreign Key Not Found" ON 3
        END
    SQL "SELECT ref from user WHERE ref='$input.manager';" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY scheme WITH input ERROR "Foreign Key Not Found" ON 6
        END
    SQL "UPDATE scheme SET scheme_id='$input.scheme_id',title='$input.title',programme_code='$input.programme_code',costcode='$input.costcode',status=$input.status,manager='$input.manager' WHERE recnum = $input.recnum ;"
    DISPLAY scheme

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY scheme

[PROCESS yes]
    SQL "DELETE FROM scheme WHERE recnum = $input.recnum ;"
    DISPLAY scheme

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT recnum, scheme_id, title FROM scheme WHERE " TO query
    IF NOT BLANK(input.scheme_id) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "scheme_id like '%" input.scheme_id "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.title) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "title like '%" input.title "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.programme_code) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "programme_code like '%" input.programme_code "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.costcode) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "costcode like '%" input.costcode "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.manager) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "manager like '%" input.manager "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY scheme_id, title;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, scheme_id,title,programme_code,costcode,status,manager FROM scheme WHERE recnum=$input.recnum ;" TO results
    DISPLAY scheme WITH results

[PROCESS pick_display]
    DISPLAY scheme WITH input

[PROCESS pick_programme_code]
    DATA results
    SQL "SELECT code, description from programme ; " TO results
    OUTPUT pick_programme_code WITH results

[OUTPUT pick_programme_code]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        programme_code : LEFT : "Code"
        description : LEFT : "Description"
        } LINK TO PROCESS pick_display WITH VALUE programme_code

[PROCESS pick_manager]
    DATA results
    SQL "SELECT ref, name from user WHERE user_type = 1; " TO results
    OUTPUT pick_manager WITH results

[OUTPUT pick_manager]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        manager : LEFT : "Ref"
        Name : LEFT : "Name"
        } LINK TO PROCESS pick_display WITH VALUE manager

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        scheme_id : LEFT : "scheme_id"
        title : LEFT : "title"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE invoice]
    SET default_process AS "clear"

[FORM invoice]
    SET title AS "Invoice Entry"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        obj_code : 7 : "Objective Code: " : PICK PROCESS "pick_obj_code"
        sub_code : 7 : "Subjective Code: " : PICK PROCESS "pick_sub_code"
        supplier : 8 : "Supplier Ref.: " : PICK PROCESS "pick_supplier"
        invoice_number : 20 : "Invoice Number: " : TEXT
        total : 12 : "Invoice Value (Net.): " : TEXT
        invoice_date : 12 : "Invoice Date: " : TEXT
        processed : 12 : "Date Processed: " : TEXT
        vat : 12 : "Non-Reclaimable VAT: " : TEXT
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "invoices"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        obj_code : 7 : "Objective Code: " : TEXT
        sub_code : 7 : "Subjective Code: " : TEXT
        supplier : 8 : "Supplier: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    INT spend
    INT amend
    INT limit
    STRING high
    LEFT input.sub_code TO high 4
    SQL "SELECT * FROM invoice WHERE  obj_code='$input.obj_code' AND sub_code='$input.sub_code' AND supplier='$input.supplier' AND invoice_number='$input.invoice_number';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT costcode from scheme WHERE costcode='$input.obj_code' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Foreign Key Not Found" ON 1
        END
    SQL "SELECT costcode from subjective WHERE costcode='$input.sub_code' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Foreign Key Not Found" ON 2
        END
    SQL "SELECT ref from supplier WHERE ref='$input.supplier' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Foreign Key Not Found" ON 3
        END
    SQL "SELECT sum(total) FROM invoice WHERE obj_code='$input.obj_code' AND sub_code='$input.sub_code';" TO results
    CALC ( results.0.0 + input.total ) TO spend
    SQL "SELECT max(amend_number) FROM approval, scheme WHERE scheme.costcode='$input.obj_code' AND approval.scheme_id=scheme.scheme_id AND ((sub_code='$input.sub_code') OR (sub_code='$high'));" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "No Approval for Expenditure" ON 2
        END
    CALC ( results.0.0 ) TO amend
    SQL "SELECT limit, sub_code FROM approval, scheme WHERE scheme.costcode='$input.obj_code' AND approval.scheme_id=scheme.scheme_id AND ((sub_code='$input.sub_code') OR (sub_code='$high')) AND amend_number=$amend ORDER BY sub_code;" TO results
    CALC ( results.0.0 - spend ) TO amend
    IF ( amend < 0 ) BEGIN
        DISPLAY invoice WITH input ERROR "Expenditure Exceeds Approval" ON 5
        END
    SQL "SELECT max(recnum) FROM invoice;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO invoice (recnum,obj_code,sub_code,supplier,invoice_number,total,invoice_date,processed,vat) VALUES ($new_recnum , '$input.obj_code', '$input.sub_code', '$input.supplier', '$input.invoice_number', $input.total, '$input.invoice_date', '$input.processed', $input.vat);"
    DISPLAY invoice

[PROCESS update]
    DATA results
    INT no_rows
    SQL "SELECT costcode from scheme WHERE costcode='$input.obj_code' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Foreign Key Not Found" ON 1
        END
    SQL "SELECT costcode from subjective WHERE costcode=$input.sub_code ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Foreign Key Not Found" ON 2
        END
    SQL "SELECT ref from supplier WHERE ref='$input.supplier' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY invoice WITH input ERROR "Foreign Key Not Found" ON 3
        END
    SQL "UPDATE invoice SET obj_code='$input.obj_code',sub_code='$input.sub_code',supplier='$input.supplier',invoice_number='$input.invoice_number',total=$input.total,invoice_date='$input.invoice_date',processed='$input.processed',vat=$input.vat WHERE recnum = $input.recnum ;"
    DISPLAY invoice

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY invoice

[PROCESS yes]
    SQL "DELETE FROM invoice WHERE recnum = $input.recnum ;"
    DISPLAY invoice

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT recnum, supplier, invoice_number, obj_code, sub_code, total FROM invoice WHERE " TO query
    IF NOT BLANK(input.obj_code) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "obj_code like '%" input.obj_code "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.sub_code) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "sub_code like '%" input.sub_code "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.supplier) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "supplier like '%" input.supplier "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.invoice_number) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "invoice_number like '%" input.invoice_number "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY supplier, invoice_number, obj_code, sub_code, total;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, obj_code,sub_code,supplier,invoice_number,total,invoice_date,processed,vat FROM invoice WHERE recnum=$input.recnum ;" TO results
    DISPLAY invoice WITH results

[PROCESS pick_display]
    DISPLAY invoice WITH input

[PROCESS pick_obj_code]
    DATA results
    SQL "SELECT costcode, title, scheme_id from scheme ; " TO results
    OUTPUT pick_obj_code WITH results

[OUTPUT pick_obj_code]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        obj_code : LEFT : "Costcode"
        title : LEFT : "Title"
        scheme_id : LEFT : "Scheme Number"
        } LINK TO PROCESS pick_display WITH VALUE obj_code

[PROCESS pick_sub_code]
    DATA results
    SQL "SELECT costcode, description from subjective ; " TO results
    OUTPUT pick_sub_code WITH results

[OUTPUT pick_sub_code]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        sub_code : LEFT : "Costcode"
        description : LEFT : "Description"
        } LINK TO PROCESS pick_display WITH VALUE sub_code

[PROCESS pick_supplier]
    DATA results
    SQL "SELECT ref, name from supplier ORDER BY name; " TO results
    OUTPUT pick_supplier WITH results

[OUTPUT pick_supplier]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        supplier : LEFT : "Reference"
        name : LEFT : "Name"
        } LINK TO PROCESS pick_display WITH VALUE supplier

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        supplier : LEFT : "supplier"
        invoice_number : LEFT : "invoice_number"
        obj_code : LEFT : "obj_code"
        sub_code : LEFT : "sub_code"
        total : LEFT : "total"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE cashflow]
    SET default_process AS "clear"

[FORM cashflow]
    SET title AS "Cashflow"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        scheme_id : 6 : "Scheme Number: " : PICK PROCESS "pick_scheme_id"
        year : 10 : "Financial Year: " : TEXT
        } IN COLUMN
    DEFINE FORM {
        month : 10 : "Month" : SEQUENCE FROM 1 DISPLAY AS "April" "May" "June" "July" "August" "September" "October" "November" "December" "January" "February" "March"
        amount : 12 : "Amount" : TEXT
        } IN TABLE OF 12 ROWS
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "schemes"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        scheme_id : 6 : "Scheme Number: " : TEXT
        year : 10 : "Year: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT a
    INT new_recnum
    SQL "SELECT * FROM cashflow WHERE  scheme_id='$input.scheme_id' AND year=$input.year;" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY cashflow WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT scheme_id from scheme WHERE scheme_id='$input.scheme_id';" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY cashflow WITH input ERROR "Foreign Key Not Found" ON 1
        END
    SQL "SELECT max(recnum) FROM cashflow;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    FOR a FROM 0 TO 11 BEGIN
        SQL "INSERT INTO cashflow (recnum,scheme_id,month,year,amount) VALUES ($new_recnum , '$input.scheme_id', $input.month.a, $input.year, $input.amount.a);"
        END
    DISPLAY cashflow

[PROCESS update]
    DATA results
    INT no_rows
    INT a
    SQL "SELECT scheme_id from scheme WHERE scheme_id='$input.scheme_id' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY cashflow WITH input ERROR "Foreign Key Not Found" ON 1
        END
    FOR a FROM 0 TO 11 BEGIN
        SQL "UPDATE cashflow SET scheme_id='$input.scheme_id',month=$input.month.a,year=$input.year,amount=$input.amount.a WHERE recnum = $input.recnum AND month = $input.month.a;"
        END
    DISPLAY cashflow

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY cashflow

[PROCESS yes]
    SQL "DELETE FROM cashflow WHERE recnum = $input.recnum ;"
    DISPLAY cashflow

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT distinct recnum, scheme_id, year FROM cashflow WHERE " TO query
    IF NOT BLANK(input.scheme_id) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "scheme_id like '%" input.scheme_id "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY scheme_id, year;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, scheme_id, year, month, amount FROM cashflow WHERE recnum=$input.recnum ORDER BY month;" TO results
    DISPLAY cashflow WITH results

[PROCESS pick_display]
    DISPLAY cashflow WITH input

[PROCESS pick_scheme_id]
    DATA results
    SQL "SELECT scheme_id, title from scheme ; " TO results
    OUTPUT pick_scheme_id WITH results

[OUTPUT pick_scheme_id]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        scheme_id : LEFT : "Scheme"
        title : LEFT : "Title
        } LINK TO PROCESS pick_display WITH VALUE scheme_id

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        scheme_id : LEFT : "scheme_id"
        year : LEFT : "year"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE approval]
    SET default_process AS "clear"

[FORM approval]
    SET title AS "Approval Entry"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        scheme_id : 6 : "Scheme Number: " : PICK PROCESS "pick_scheme_id"
        amend_number : 10 : "Amendment Number: " : TEXT
        sub_code : 7 : "Subjective Costcode: " : PICK PROCESS "pick_sub_code"
        limit : 12 : "Expenditure Limit: " : TEXT
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "schemes"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        scheme_id : 6 : "Scheme Number: " : TEXT
        amend_number : 10 : "Amendment Number: " : TEXT
        sub_code : 7 : "Subjective Code: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    SQL "SELECT * FROM approval WHERE  scheme_id='$input.scheme_id' AND amend_number=$input.amend_number AND sub_code='$input.sub_code';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY approval WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT scheme_id from scheme WHERE scheme_id='$input.scheme_id' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY approval WITH input ERROR "Foreign Key Not Found" ON 1
        END
    SQL "SELECT costcode from subjective WHERE costcode='$input.sub_code' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY approval WITH input ERROR "Foreign Key Not Found" ON 3
        END
    SQL "SELECT max(recnum) FROM approval;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO approval (recnum,scheme_id,amend_number,sub_code,limit) VALUES ($new_recnum , '$input.scheme_id', $input.amend_number, '$input.sub_code', $input.limit);"
    DISPLAY approval

[PROCESS update]
    DATA results
    INT no_rows
    SQL "SELECT scheme_id from scheme WHERE scheme_id='$input.scheme_id' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY approval WITH input ERROR "Foreign Key Not Found" ON 1
        END
    SQL "SELECT costcode from subjective WHERE costcode='$input.sub_code' ;" TO results
    ROWS results TO no_rows
    IF no_rows = 0 THEN BEGIN
        DISPLAY approval WITH input ERROR "Foreign Key Not Found" ON 3
        END
    SQL "UPDATE approval SET scheme_id='$input.scheme_id',amend_number=$input.amend_number,sub_code='$input.sub_code',limit=$input.limit WHERE recnum = $input.recnum ;"
    DISPLAY approval

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY approval

[PROCESS yes]
    SQL "DELETE FROM approval WHERE recnum = $input.recnum ;"
    DISPLAY approval

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 1 TO and
    MOVE "SELECT recnum, scheme_id, amend_number, sub_code, description FROM approval, subjective WHERE subjective.costcode=approval.sub_code " TO query
    IF NOT BLANK(input.scheme_id) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "scheme_id like '%" input.scheme_id "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.sub_code) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "sub_code like '%" input.sub_code "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY scheme_id, amend_number;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, scheme_id,amend_number,sub_code, limit FROM approval WHERE recnum=$input.recnum ;" TO results
    DISPLAY approval WITH results

[PROCESS pick_display]
    DISPLAY approval WITH input

[PROCESS pick_scheme_id]
    DATA results
    SQL "SELECT scheme_id, title from scheme ; " TO results
    OUTPUT pick_scheme_id WITH results

[OUTPUT pick_scheme_id]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        scheme_id : LEFT : "Scheme"
        title : LEFT : "Title"
        } LINK TO PROCESS pick_display WITH VALUE scheme_id

[PROCESS pick_sub_code]
    DATA results
    SQL "SELECT costcode, description from subjective ; " TO results
    OUTPUT pick_sub_code WITH results

[OUTPUT pick_sub_code]
    SET title AS "Available choices:"
    SET type AS screen
    DEFINE  OUTPUT {
        sub_code : LEFT : "sub_code"
        description : LEFT : "Description"
        } LINK TO PROCESS pick_display WITH VALUE sub_code

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        scheme_id : LEFT : "Scheme"
        amend_number : LEFT : "Amendment"
        sub_code : LEFT : "Subjective"
        description : LEFT : "Description"
        } LINK TO PROCESS find_display WITH VALUE recnum


[MODULE user]
    SET default_process AS "clear"

[FORM user]
    SET title AS "User Maintenance"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        ref : 6 : "User Reference: " : TEXT
        name : 45 : "Full Name: " : TEXT
        password : 8 : "Password: " : TEXT
        user_type : 10 : "Type: " : CHOICE "Invoice Logger" "Scheme Manager" "System Manager"
        } IN COLUMN
    DEFINE TOOLBAR {
        "ADD" : ALL : PROCESS "add"
        "FIND" : ALL : PROCESS "find"
        "UPDATE" : DATA : PROCESS "update"
        "DELETE" : DATA : PROCESS "delete"
        "CLEAR" : ALL : PROCESS "clear"
        "EXIT" : ALL : MENU "system"
        }

[FORM sure]
    SET title AS "Are you sure you want to delete ?"
    DEFINE FORM {
        recnum : 10 : "" : HIDDEN
        ref : 6 : "User Reference: " : TEXT
        }
    DEFINE TOOLBAR {
        "YES" : ALL : PROCESS "yes"
        "NO" : ALL : PROCESS "clear"
        }

[PROCESS add]
    DATA results
    INT no_rows
    INT new_recnum
    SQL "SELECT * FROM user WHERE  ref='$input.ref';" TO results
    ROWS results TO no_rows
    IF no_rows > 0 THEN BEGIN
        DISPLAY user WITH input ERROR "Duplicate Record" ON 1
        END
    SQL "SELECT max(recnum) FROM user;" TO results
    CALC ( results.0.0 + 1 ) TO new_recnum
    SQL "INSERT INTO user (recnum,ref,name,password,user_type) VALUES ($new_recnum , '$input.ref', '$input.name', '$input.password', $input.user_type);"
    DISPLAY user

[PROCESS update]
    DATA results
    INT no_rows
    SQL "UPDATE user SET ref='$input.ref',name='$input.name',password='$input.password',user_type=$input.user_type WHERE recnum = $input.recnum ;"
    DISPLAY user

[PROCESS delete]
    DISPLAY sure WITH input

[PROCESS clear]
    DISPLAY user

[PROCESS yes]
    SQL "DELETE FROM user WHERE recnum = $input.recnum ;"
    DISPLAY user

[PROCESS find]
    DATA results
    INT and
    STRING query
    MOVE 0 TO and
    MOVE "SELECT recnum, ref, name FROM user WHERE " TO query
    IF NOT BLANK(input.ref) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "ref like '%" input.ref "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.name) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "name like '%" input.name "%'"
        MOVE 1 TO and
        END
    IF NOT BLANK(input.password) THEN BEGIN
        IF and = 1 THEN APPEND query " AND "
        APPEND query "password like '%" input.password "%'"
        MOVE 1 TO and
        END
    APPEND query "ORDER BY ref, name;"
    SQL query TO results
    OUTPUT find_list WITH results

[PROCESS find_display]
    DATA results
    SQL "SELECT recnum, ref,name,password,user_type FROM user WHERE recnum=$input.recnum ;" TO results
    DISPLAY user WITH results

[PROCESS pick_display]
    DISPLAY user WITH input

[OUTPUT find_list]
    SET title AS "Search Results"
    SET type AS screen
    DEFINE OUTPUT {
        recnum : RIGHT : "Record"
        ref : LEFT : "ref"
        name : LEFT : "name"
        } LINK TO PROCESS find_display WITH VALUE recnum



[]